package com.asdan.seededu.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import com.asdan.seededu.entity.security.School;
import com.asdan.seededu.entity.security.Student;
import com.asdan.seededu.entity.security.SubProject;
import com.asdan.seededu.service.StudentService;
import com.asdan.seededu.util.DatabaseAccess;
import com.asdan.seededu.util.MD5;
import com.asdan.seededu.util.Pager;
import com.asdan.seededu.util.Util;

public class StudentServiceImpl extends UtilImpl implements StudentService {
	

	public Pager<Student> getStudents(String name, String schoolId,
			String grade, String cellPhone, String subProId ,int pageNo) {
		
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		List<Student> stuList = new ArrayList<Student>()  ;
		Pager<Student> page = new Pager<Student>() ;
		String hql = "" ;
		if(name !=null&&!"".equals(name)){
			hql += " AND name ='" +name +"' " ;
		}
		if(schoolId !=null&&!"".equals(schoolId)){
			hql += " AND schoolId = "+ schoolId;
		}
		if(grade !=null&&!"".equals(grade)){
			hql += " AND grade ='" + grade +"' " ;
		}
		if(cellPhone !=null&&!"".equals(cellPhone)){
			hql += " AND cellPhone ='" +cellPhone +"'" ;
			
		}
		if(subProId !=null&&!"".equals(subProId)){
			hql += "AND subProId=" + subProId ;
		}
		String sql = "select suid , name , schoolId , grade ,email ,cellPhone,SCHOOLID,SCHOOLREGION,SCHOOLCITY,SCHOOLNAME" +
				",SCHOOLENGLISHNAME,SCHOOLADDRESS,SCHOOLURL  from v_student  where  ifdelete = 0";
		try{
			String fenyesql = " LIMIT " +Util.pageCount*(pageNo-1) + ","+Util.pageCount  ;
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql + hql + fenyesql);
			System.out.println(sql + hql + fenyesql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				
				Student stu= new Student();
				School sch = new School() ;
				
  				stu.setSuid(rs.getInt("SUID")) ;
				stu.setName(rs.getString("NAME")) ;
				stu.setSchoolId(rs.getInt("SCHOOLID")) ;
				stu.setGrade(rs.getString("GRADE")) ;
				stu.setEmail(rs.getString("EMAIL")) ;
				stu.setCellPhone(rs.getString("CELLPHONE")) ;
				
				sch.setSchoolId(rs.getInt("SCHOOLID")) ;
				sch.setSchoolREGION(rs.getString("SCHOOLREGION")) ;
				sch.setSchoolCITY(rs.getString("SCHOOLCITY")) ;
				sch.setSchoolNAME(rs.getString("SCHOOLNAME") ) ;
				sch.setSchoolENGLISHNAME(rs.getString("SCHOOLENGLISHNAME")) ;
				sch.setSchoolADDRESS(rs.getString("SCHOOLADDRESS")) ;
				sch.setSchoolURL(rs.getString("SCHOOLURL")) ;
				
				stu.setSchool(sch) ;
				stuList.add(stu) ;
				
			}
			int total = this.gettotalCount(hql +"AND ifdelete = 0" ,cn , rs , pstmt) ;
			page.setTotal(total) ;
			page.setTotalPage(total) ;
			if(stuList.size()>0){
				
				for(int i=0;i<stuList.size() ;i++){
				Student stu = stuList.get(i) ;
				System.out.println(stu.getSuid()) ;
				sql = "select SUBPROJECTID  , SUBPROJECTNAME from v_projcet_student where suid = ? ";
				pstmt = cn.prepareStatement(sql);
				pstmt.setInt(1, stu.getSuid());
				rs = pstmt.executeQuery();
				List<SubProject>  subList = new ArrayList<SubProject>() ;
				while(rs.next()){
					SubProject subp = new  SubProject() ;
					subp.setSpid(rs.getInt("SUBPROJECTID")) ;
					subp.setName(rs.getString("SUBPROJECTNAME")) ;
					subList.add(subp) ;
				}
					stu.setSubProjects(subList) ;
				}
			}
			page.setDatas(stuList) ;
			System.out.println(page.getDatas().size());
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}
		}
		return page ;
		
	}
	public Student getStudent(String uid) {
		// TODO Auto-generated method stub
		return null;
	}

	public boolean addStudent(Student stu) {
		// TODO Auto-generated method stub
		boolean  temp = true ;
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		String pw=MD5.md5s("123456");
		stu.setPassword(pw) ;
 		String sql="insert into student(name , password , schoolId , grade , email , cellPhone) values(?,?,?,?,?,?) ";
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setString(1, stu.getName());
			pstmt.setString(2, stu.getPassword()) ;
			pstmt.setInt(3, stu.getSchoolId()) ;
			pstmt.setString(4, stu.getGrade()) ;
			pstmt.setString(5, stu.getEmail()) ;
			pstmt.setString(6,stu.getCellPhone()) ;
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			temp = false ;
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
					temp = false ;
			}
		}
		return temp;
	}

	public boolean updateStudent(Student stu) {
		boolean temp = true ;
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		String sql = "update student  set  name=? ,grade=? ,cellPhone=? ,QQ=?,sex=?,birthday=?,firstName=?," +
				"lastName=?,idCard=?,passid=?,passCloseTime=?,address=?,postCode=?,fatherName=?,fatherPhone=?,fatherEmail=?,motherName=?,motherPhone=?,motherEmail=?  where suid= ?" ;
		System.out.println(sql) ;
		try {
			cn = databaseAccess.getConn() ;
			pstmt = cn.prepareStatement(sql) ;
			pstmt.setString(1,stu.getName()) ;
			pstmt.setString( 2, stu.getGrade()) ;
			pstmt.setString(3, stu.getCellPhone()) ;
			pstmt.setString(4, stu.getQQ()) ;
			pstmt.setString( 5, stu.getSex()) ;
			pstmt.setLong(6,stu.getBirthday()) ;
			pstmt.setString( 7, stu.getFirstName()) ;
			pstmt.setString(8, stu.getLastName()) ;
			pstmt.setString(9, stu.getIdCard() ) ;
			pstmt.setString( 10,stu.getPassid()) ;
			pstmt.setLong(11,stu.getPassCloseTime()) ;
			pstmt.setString( 12,stu.getAddress() ) ;
			pstmt.setString(13,stu.getPostCode()) ;
			pstmt.setString(14, stu.getFatherName() ) ;
			pstmt.setString( 15,stu.getFatherPhone()) ;
			pstmt.setString(16, stu.getFatherEmail() ) ;
			pstmt.setString( 17,stu.getMotherName()) ;
			pstmt.setString(18, stu.getMotherPhone() ) ;
			pstmt.setString( 19,stu.getMotherEmail()) ;
			pstmt.setInt(20,stu.getSuid()) ;
			
			pstmt.executeUpdate() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			temp = false ;
			e.printStackTrace();
		}finally{
			try {
				pstmt.close() ;
				cn.close() ;
				databaseAccess.close() ;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
		return temp;
	}

	public boolean deleteStudent(String uid) {
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		boolean result = true;
		Connection cn = null;
		PreparedStatement pstmt=null;
		String hql = "UPDATE STUDENT SET ifdelete =?  WHERE SUID=? ";
		System.out.println(hql);
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(hql);
			pstmt.setInt(1, 1);
			pstmt.setInt(2, Integer.parseInt(uid));
			pstmt.execute();
			result = true;
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			result = false ;
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close() ;
				databaseAccess.close() ;
			}catch(Exception ex){
				result = false ;
			}
		}
		return result;
	}
	public Student getStudentByEmailOrID(String email , int ID){
		
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		Student stu = null ;
		String sql = "select suid  , name , schoolId , grade ,email ,cellPhone ,QQ ,wchart ,weibo ," +
				"screatTime,sex,birthday,firstName,lastName,idCard,passid,passSignTime,passCloseTime" +
				",address,postCode,fatherName,fatherPhone,fatherEmail,motherName,motherPhone,motherEmail from STUDENT  where (email =? or suid = ?) AND ifdelete = 0  ";
		System.out.println(sql);
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setString(1, email);
			pstmt.setInt(2,ID ) ;
			rs = pstmt.executeQuery();
			while(rs.next()){
				stu= new Student();
  				stu.setSuid(rs.getInt("SUID")) ;
				stu.setName(rs.getString("NAME")) ;
				stu.setSchoolId(rs.getInt("SCHOOLID")) ;
				stu.setGrade(rs.getString("GRADE")) ;
				stu.setEmail(rs.getString("EMAIL")) ;
				stu.setCellPhone(rs.getString("CELLPHONE")) ;
				stu.setQQ(rs.getString("QQ")) ;
				stu.setWchart(rs.getString("wchart")) ;
				stu.setWeibo(rs.getString("weibo")) ;
				stu.setScreatTime(rs.getLong("screatTime")) ;
				stu.setSex(rs.getString("sex")) ; 
				stu.setBirthday(rs.getLong("birthday")) ;
				stu.setFirstName(rs.getString("firstname")) ;
				stu.setLastName(rs.getString("lastname")) ;
				stu.setIdCard(rs.getString("idCard")) ;
				stu.setPassid(rs.getString("passid")) ;
				stu.setPassSignTime(rs.getLong("PassSignTime")) ;
				stu.setPassCloseTime(rs.getLong("passCloseTime")) ;
				stu.setAddress(rs.getString("address")) ;
				stu.setPostCode(rs.getString("postCode")) ;
				stu.setFatherName(rs.getString("fatherName")) ;
				stu.setFatherPhone(rs.getString("fatherPhone")) ;
				stu.setFatherEmail(rs.getString("fatherEmail")) ;
				stu.setMotherName(rs.getString("motherName")) ;
				stu.setMotherPhone(rs.getString("motherPhone")) ;
				stu.setMotherEmail(rs.getString("motherEmail")) ;
			}
			if(stu!=null){
				sql = "select SUBPROJECTID  , SUBPROJECTNAME from v_projcet_student where suid = ? ";
				pstmt = cn.prepareStatement(sql);
				pstmt.setInt(1, stu.getSuid());
				rs = pstmt.executeQuery();
				List<SubProject>  subList = new ArrayList<SubProject>() ;
				while(rs.next()){
					SubProject subp = new  SubProject() ;
					subp.setSpid(rs.getInt("SUBPROJECTID")) ;
					subp.setName(rs.getString("SUBPROJECTNAME")) ;
					subList.add(subp) ;
				}
				stu.setSubProjects(subList) ;
				
				sql = "select SCHOOLID ,SCHOOLREGION ,SCHOOLCITY ,SCHOOLNAME, SCHOOLENGLISHNAME,SCHOOLADDRESS,SCHOOLURL  from school where schoolId  = ? ";
				pstmt = cn.prepareStatement(sql);
				pstmt.setInt(1, stu.getSchoolId() );
				rs = pstmt.executeQuery();
				School sch =null ;
				while(rs.next()){
					sch = new  School() ;
					sch.setSchoolId(rs.getInt("SCHOOLID")) ;
					sch.setSchoolREGION(rs.getString("SCHOOLREGION")) ;
					sch.setSchoolCITY(rs.getString("SCHOOLCITY")) ;
					sch.setSchoolNAME(rs.getString("SCHOOLNAME") ) ;
					sch.setSchoolENGLISHNAME(rs.getString("SCHOOLENGLISHNAME")) ;
					sch.setSchoolADDRESS(rs.getString("SCHOOLADDRESS")) ;
					sch.setSchoolURL(rs.getString("SCHOOLURL")) ;
					
				}
				stu.setSchool(sch) ;
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}
		}
		return stu ;
	}
	

	public int gettotalCount(String sql,Connection cn ,ResultSet rs ,PreparedStatement pstmt) {
		// TODO Auto-generated method stub
		return gettotalCount(sql, "STUDENT", cn , rs , pstmt );
	}
	

}
